﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SQLite;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace Clipper.DAL
{
    public class StandardCurveData
    {

        /// <summary>
        /// DAL 更新数据库数据
        /// </summary>
        /// <returns></returns>
        public bool update(Model.DataBaseModel.StandardCurveData model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update StandardCurveData set ");
            strSql.Append("concentration1=@concentration1,");
            strSql.Append("detectResult1=@detectResult1,");
            strSql.Append("concentration2=@concentration2,");
            strSql.Append("detectResult2=@detectResult2,");
            strSql.Append("concentration3=@concentration3,");
            strSql.Append("detectResult3=@detectResult3,");
            strSql.Append("concentration4=@concentration4,");
            strSql.Append("detectResult4=@detectResult4,");
            strSql.Append("concentration5=@concentration5,");
            strSql.Append("detectResult5=@detectResult5,");
            strSql.Append("concentration6=@concentration6,");
            strSql.Append("detectResult6=@detectResult6,");
            strSql.Append("fileTime=@fileTime,");
            strSql.Append("lineValue=@lineValue,");
            strSql.Append("ordinaryLineValue=@ordinaryLineValue,");
            strSql.Append("coefficientB=@coefficientB,");
            strSql.Append("constantA=@constantA,");
            strSql.Append("ordinaryCoefficientB=@ordinaryCoefficientB,");
            strSql.Append("ordinaryConstantA=@ordinaryConstantA,");
            strSql.Append("closeDate=@closeDate,");
            strSql.Append("batchNumber=@batchNumber ");
            strSql.Append(" where detectItem=@detectItem ");
            SQLiteParameter[] parameters = {
                    new SQLiteParameter("@concentration1",DbType.Double),
                    new SQLiteParameter("@detectResult1", DbType.Double),
                    new SQLiteParameter("@concentration2",DbType.Double),
                    new SQLiteParameter("@detectResult2", DbType.Double),
                    new SQLiteParameter("@concentration3",DbType.Double),
                    new SQLiteParameter("@detectResult3", DbType.Double),
                    new SQLiteParameter("@concentration4",DbType.Double),
                    new SQLiteParameter("@detectResult4", DbType.Double),
                    new SQLiteParameter("@concentration5",DbType.Double),
                    new SQLiteParameter("@detectResult5", DbType.Double),
                    new SQLiteParameter("@concentration6",DbType.Double),
                    new SQLiteParameter("@detectResult6", DbType.Double),
                    new SQLiteParameter("@fileTime", DbType.DateTime),
                    new SQLiteParameter("@lineValue", DbType.Double),
                    new SQLiteParameter("@ordinaryLineValue", DbType.Double),
                    new SQLiteParameter("@coefficientB", DbType.Double),
                    new SQLiteParameter("@constantA", DbType.Double),
                    new SQLiteParameter("@ordinaryCoefficientB", DbType.Double),
                    new SQLiteParameter("@ordinaryConstantA", DbType.Double),
                    new SQLiteParameter("@closeDate",DbType.DateTime),
                    new SQLiteParameter("@batchNumber",DbType.Int32),
                new SQLiteParameter("@detectItem", DbType.String,200)};

            parameters[0].Value = model.Concentration1;
            parameters[1].Value = model.DetectResult1;
            parameters[2].Value = model.Concentration2;
            parameters[3].Value = model.DetectResult2;
            parameters[4].Value = model.Concentration3;
            parameters[5].Value = model.DetectResult3;
            parameters[6].Value = model.Concentration4;
            parameters[7].Value = model.DetectResult4;
            parameters[8].Value = model.Concentration5;
            parameters[9].Value = model.DetectResult5;
            parameters[10].Value = model.Concentration6;
            parameters[11].Value = model.DetectResult6;
            parameters[12].Value = model.FileTime;
            parameters[13].Value = model.LineValue;
            parameters[14].Value = model.OrdinaryLineValue;
            parameters[15].Value = model.CoefficientB;
            parameters[16].Value = model.ConstantA;
            parameters[17].Value = model.OrdinaryCoefficientB;
            parameters[18].Value = model.OrdinaryConstantA;
            parameters[19].Value = model.CloseDate;
            parameters[20].Value = model.BatchNumber;
            parameters[21].Value = model.DetectItem;

            int rows = SQLiteHelper.ExecuteSql(strSql.ToString(), parameters);
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        /// <summary>
		/// 是否存在该记录
		/// </summary>
		public bool Exists(string detectItem)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select count(1) from StandardCurveData");
            strSql.Append(" where detectItem=@detectItem ");
            SQLiteParameter[] parameters = {
                    new SQLiteParameter("@detectItem", DbType.String)          };
            parameters[0].Value = detectItem;

            return SQLiteHelper.Exists(strSql.ToString(), parameters);
        }



        /// <summary>
        /// DAL 查询全部检测项
        /// </summary>
        /// <param name="strWhere"></param>
        /// <returns></returns>
        public DataSet seletAll(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select detectItem ");
            strSql.Append(" FROM StandardCurveData ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            return SQLiteHelper.Query(strSql.ToString());
        }



        /// <summary>
        /// DAL 插入一条数据
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public bool insertData(Model.DataBaseModel.StandardCurveData model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("insert into StandardCurveData (");
           // strSql.Append("detectItem,concentration1,detectResult1,concentration2,detectResult2,concentration3,detectResult3,concentration4,detectResult4,concentration5,detectResult5,concentration6,detectResult6 )");
            strSql.Append("detectItem,concentration1,detectResult1,concentration2,detectResult2,concentration3,detectResult3,concentration4,detectResult4,concentration5,detectResult5,concentration6,detectResult6,fileTime,lineValue,ordinaryLineValue,coefficientB,constantA,ordinaryCoefficientB,ordinaryConstantA,closeDate,batchNumber )");
            strSql.Append(" values (");
            //strSql.Append("@detectItem,@concentration1,@detectResult1,@concentration2,@detectResult2,@concentration3,@detectResult3,@concentration4,@detectResult4,@concentration5,@detectResult5,@concentration6,@detectResult6 )");
            strSql.Append("@detectItem,@concentration1,@detectResult1,@concentration2,@detectResult2,@concentration3,@detectResult3,@concentration4,@detectResult4,@concentration5,@detectResult5,@concentration6,@detectResult6,@fileTime,@lineValue,@ordinaryLineValue,@coefficientB,@constantA,@ordinaryCoefficientB,@ordinaryConstantA,@closeDate,@batchNumber )");
            SQLiteParameter[] parameters = {
                    new SQLiteParameter("@detectItem", DbType.String,200),
                    new SQLiteParameter("@concentration1", DbType.Double,160),
                    new SQLiteParameter("@detectResult1", DbType.Double,160),
                     new SQLiteParameter("@concentration2", DbType.Double,160),
                    new SQLiteParameter("@detectResult2", DbType.Double,160),
                     new SQLiteParameter("@concentration3", DbType.Double,160),
                    new SQLiteParameter("@detectResult3", DbType.Double,160),
                     new SQLiteParameter("@concentration4", DbType.Double,160),
                    new SQLiteParameter("@detectResult4", DbType.Double,160),
                     new SQLiteParameter("@concentration5", DbType.Double,160),
                    new SQLiteParameter("@detectResult5", DbType.Double,160),
                     new SQLiteParameter("@concentration6", DbType.Double,160),
                    new SQLiteParameter("@detectResult6", DbType.Double,160),
                    new SQLiteParameter("@fileTime", DbType.DateTime),
                    new SQLiteParameter("@lineValue", DbType.Double,160),
                    new SQLiteParameter("@ordinaryLineValue", DbType.Double,160),
                    new SQLiteParameter("@coefficientB", DbType.Double,160),
                    new SQLiteParameter("@constantA", DbType.Double,160),
                    new SQLiteParameter("@ordinaryCoefficientB", DbType.Double,160),
                    new SQLiteParameter("@ordinaryConstantA", DbType.Double,160),
                    new SQLiteParameter("@closeDate",DbType.DateTime),
                    new SQLiteParameter("@batchNumber",DbType.Int32)
            };
            parameters[0].Value = model.DetectItem;
            parameters[1].Value = model.Concentration1;
            parameters[2].Value = model.DetectResult1;
            parameters[3].Value = model.Concentration2;
            parameters[4].Value = model.DetectResult2;
            parameters[5].Value = model.Concentration3;
            parameters[6].Value = model.DetectResult3;
            parameters[7].Value = model.Concentration4;
            parameters[8].Value = model.DetectResult4;
            parameters[9].Value = model.Concentration5;
            parameters[10].Value = model.DetectResult5;
            parameters[11].Value = model.Concentration6;
            parameters[12].Value = model.DetectResult6;
            parameters[13].Value = model.FileTime;
            parameters[14].Value = model.LineValue;
            parameters[15].Value = model.OrdinaryLineValue;
            parameters[16].Value = model.CoefficientB;
            parameters[17].Value = model.ConstantA;
            parameters[18].Value = model.OrdinaryCoefficientB;
            parameters[19].Value = model.OrdinaryConstantA;
            parameters[20].Value = model.CloseDate;
            parameters[21].Value = model.BatchNumber;
            int rows = SQLiteHelper.ExecuteSql(strSql.ToString(), parameters);
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }


        /// <summary>
        /// 单条数据查询  获取一个对象
        /// </summary>
        /// <param name="detectItemName"></param>
        /// <returns></returns>
        public Model.DataBaseModel.StandardCurveData getModel(string detectItemName)
        {

            StringBuilder strSql = new StringBuilder();
           // strSql.Append("select id,detectItem,concentration1,detectResult1,concentration2,detectResult2,concentration3,detectResult3,concentration4,detectResult4,concentration5,detectResult5,concentration6,detectResult6 FROM StandardCurveData ");
            strSql.Append("select id,detectItem,concentration1,detectResult1,concentration2,detectResult2,concentration3,detectResult3,concentration4,detectResult4,concentration5,detectResult5,concentration6,detectResult6,fileTime,lineValue,ordinaryLineValue,coefficientB,constantA,ordinaryCoefficientB,ordinaryConstantA,closeDate,batchNumber FROM StandardCurveData ");
            strSql.Append(" where detectItem=@detectItem ");
            SQLiteParameter[] parameters = {
                    new SQLiteParameter("@detectItem", DbType.String,20)          };
            parameters[0].Value = detectItemName;

            //Clipper.Model.ReagentKit model = new Clipper.Model.ReagentKit();
            DataSet ds = SQLiteHelper.Query(strSql.ToString(), parameters);
            if (ds.Tables[0].Rows.Count > 0)
            {
                return DataRowToModel(ds.Tables[0].Rows[0]);
            }
            else
            {
                return null;
            }

        }
        /// <summary>
        /// 得到一个对象实体
        /// </summary>
        public Clipper.Model.DataBaseModel.StandardCurveData DataRowToModel(DataRow row)
        {
            Clipper.Model.DataBaseModel.StandardCurveData model = new Clipper.Model.DataBaseModel.StandardCurveData();
            if (row != null)
            {
                if (row["id"] != null && row["id"].ToString() != "")
                {
                    model.Id = int.Parse(row["id"].ToString());
                }
                if (row["detectItem"] != null && row["detectItem"].ToString() != "")
                {
                    model.DetectItem = row["detectItem"].ToString();
                }
                if (row["concentration1"] != null && row["concentration1"].ToString() != "")
                {
                    model.Concentration1 = double.Parse(row["concentration1"].ToString());
                }
                if (row["detectResult1"] != null && row["detectResult1"].ToString() != "")
                {
                    model.DetectResult1 = double.Parse(row["detectResult1"].ToString());
                }
                if (row["concentration2"] != null && row["concentration2"].ToString() != "")
                {
                    model.Concentration2 = double.Parse(row["concentration2"].ToString());
                }
                if (row["detectResult2"] != null && row["detectResult2"].ToString() != "")
                {
                    model.DetectResult2 = double.Parse(row["detectResult2"].ToString());
                }
                if (row["concentration3"] != null && row["concentration3"].ToString() != "")
                {
                    model.Concentration3 = double.Parse(row["concentration3"].ToString());
                }
                if (row["detectResult3"] != null && row["detectResult3"].ToString() != "")
                {
                    model.DetectResult3 = double.Parse(row["detectResult3"].ToString());
                }
                if (row["concentration4"] != null && row["concentration4"].ToString() != "")
                {
                    model.Concentration4 = double.Parse(row["concentration4"].ToString());
                }
                if (row["detectResult4"] != null && row["detectResult4"].ToString() != "")
                {
                    model.DetectResult4 = double.Parse(row["detectResult4"].ToString());
                }
                if (row["concentration5"] != null && row["concentration5"].ToString() != "")
                {
                    model.Concentration5 = double.Parse(row["concentration5"].ToString());
                }
                if (row["detectResult5"] != null && row["detectResult5"].ToString() != "")
                {
                    model.DetectResult5 = double.Parse(row["detectResult5"].ToString());
                }
                if (row["concentration6"] != null && row["concentration6"].ToString() != "")
                {
                    model.Concentration6 = double.Parse(row["concentration6"].ToString());
                }
                if (row["detectResult6"] != null && row["detectResult6"].ToString() != "")
                {
                    model.DetectResult6 = double.Parse(row["detectResult6"].ToString());
                }
                if (row["fileTime"] != null && row["fileTime"].ToString() != "")
                {
                    model.FileTime = DateTime.Parse(row["fileTime"].ToString());
                }
                if (row["lineValue"] != null && row["lineValue"].ToString() != "")
                {
                    model.LineValue = double.Parse(row["lineValue"].ToString());
                }
                if (row["ordinaryLineValue"] != null && row["ordinaryLineValue"].ToString() != "")
                {
                    model.OrdinaryLineValue = double.Parse(row["ordinaryLineValue"].ToString());
                }
                if (row["coefficientB"] != null && row["coefficientB"].ToString() != "")
                {
                    model.CoefficientB = double.Parse(row["coefficientB"].ToString());
                }
                if (row["constantA"] != null && row["constantA"].ToString() != "")
                {
                    model.ConstantA = double.Parse(row["constantA"].ToString());
                }
                if (row["ordinaryCoefficientB"] != null && row["ordinaryCoefficientB"].ToString() != "")
                {
                    model.OrdinaryCoefficientB = double.Parse(row["ordinaryCoefficientB"].ToString());
                }
                if (row["ordinaryConstantA"] != null && row["ordinaryConstantA"].ToString() != "")
                {
                    model.OrdinaryConstantA = double.Parse(row["ordinaryConstantA"].ToString());
                }
                if (row["closeDate"]!=null && row["closeDate"].ToString()!="")
                {
                    model.CloseDate = DateTime.Parse(row["closeDate"].ToString());
                }
                if (row["batchNumber"]!=null && row["batchNumber"].ToString()!="")
                {
                    model.BatchNumber = Int32.Parse(row["batchNumber"].ToString());
                }

            }
            return model;
        }
        /// </summary>
        public Clipper.Model.DataBaseModel.StandardCurveData DataRowToModelOne(DataRow row)
        {
            Clipper.Model.DataBaseModel.StandardCurveData model = new Clipper.Model.DataBaseModel.StandardCurveData();
            if (row != null)
            {
               
                if (row["detectItem"] != null && row["detectItem"].ToString() != "")
                {
                    model.DetectItem = row["detectItem"].ToString();
                }
               
            }
            return model;
        }

        /// <summary>
        /// DAL 更新数据库数据 仅仅更新有效截止日期和编号
        /// </summary>
        /// <returns></returns>
        public bool updateDateAndNum(Model.DataBaseModel.StandardCurveData model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update StandardCurveData set ");            
            strSql.Append("closeDate=@closeDate,");
            strSql.Append("batchNumber=@batchNumber ");
            strSql.Append(" where detectItem=@detectItem ");
            SQLiteParameter[] parameters = {                 
                    new SQLiteParameter("@closeDate", DbType.DateTime),
                   new SQLiteParameter("@batchNumber", DbType.Int32),
                    new SQLiteParameter("@detectItem", DbType.String,200)};

            parameters[0].Value = model.CloseDate;
            parameters[1].Value = model.BatchNumber;           
            parameters[2].Value = model.DetectItem;

            int rows = SQLiteHelper.ExecuteSql(strSql.ToString(), parameters);
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }



    }
}
